/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package BusinessLogic;

import DTO.Booth;
import DTO.Exhibitor;
import DTO.Zone;
import DataAccess.DataUtility;
import com.itextpdf.text.*;
import com.itextpdf.text.pdf.PdfWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Vector;

/**
 *
 * @author Tran anh
 * @version 1.0
 */
public class GenerateReports {

    /**
     * create Object boothManagement
     */
    private BoothManagement boothMag;

    /**
     * initial BoothManagement
     */
    public GenerateReports() {
        boothMag = new BoothManagement();
    }

    /**
     * Load exhibitor financial list and use library iText to
     * export data to *.pdf file
     */
    public void financialList() {
        Vector<Exhibitor> listExhibitor = loadExhibitor();
        try {
            //Create a file
            String path = "FinacialListReport.pdf";
            File file = new File(path);
            //Instantiation of document object
            Document document = new Document(PageSize.A4, 50, 50, 50, 50);
            //Creation of PdfWriter object
            PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(file));
            document.open();

            Paragraph title1 = new Paragraph("\t\tReport about Exhibitor finacial list\n\n", FontFactory.getFont(FontFactory.HELVETICA, 20, Font.NORMAL, new BaseColor(0, 0, 0)));
            Chapter chapter1 = new Chapter(title1, 1);

            chapter1.setNumberDepth(0);

            for (int i = 1; i <= listExhibitor.size(); i++) {
                Exhibitor ex = listExhibitor.get(i - 1);

                Paragraph title11 = new Paragraph(ex.getId() + " - " + ex.getName() + " belong with company " + ex.getCompany(),
                        FontFactory.getFont(FontFactory.TIMES_ROMAN, 14, Font.UNDERLINE, new BaseColor(0, 0, 0)));

                Section section = chapter1.addSection(title11);
                String str = "";
                if (ex.getPaidStatus() == 1) {
                    str = "paid " + ex.getMoney() + "$ in account";
                } else {
                    str = "still owes " + ex.getMoney() + "$ in account";
                }
                Paragraph someSectionText = new Paragraph(str);
                section.add(someSectionText);
                section.add(new Paragraph(""));

            }
            //add object chapter and close document
            document.add(chapter1);
            document.close();

            //Open file
            Runtime.getRuntime().exec("rundll32 url.dll,FileProtocolHandler " + path);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Create report booth audit trail. check in database
     * which Exhibitor was rent, export to *.pdf file
     * and open report
     */
    public void auditTrail() {
        //load list zone
        Vector<Zone> listZone = boothMag.loadZone();
        try {
            //Create a file
            String path = "booth_audit_trail.pdf";
            File file = new File(path);
            //Instantiation of document object
            Document document = new Document(PageSize.A4, 50, 50, 50, 50);
            //Creation of PdfWriter object
            PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(file));
            document.open();
            //Title
            Paragraph title1 = new Paragraph("\t\tReport about Exhibitor finacial list\n\n", FontFactory.getFont(FontFactory.HELVETICA, 20, Font.NORMAL, new BaseColor(0, 0, 0)));
            Chapter chapter1 = new Chapter(title1, 1);

            chapter1.setNumberDepth(0);

            for (int i = 1; i <= listZone.size(); i++) {
                Zone zn = listZone.get(i - 1);
                Vector<Booth> listBooth = boothMag.loadBooth(zn.getIDzone(), true);

                //print title
                Paragraph title11 = new Paragraph(zn.getIDzone() + " - " + zn.getName()  + " Size: " + zn.getSize() + " Fees: " + zn.getMoney(),
                        FontFactory.getFont(FontFactory.TIMES_ROMAN, 14, Font.UNDERLINE, new BaseColor(0, 0, 0)));

                Section section = chapter1.addSection(title11);
                //print propertise of zone
                Paragraph someSectionText = new Paragraph("Total Booth in this Zone: " + zn.getNumberBooth());
                section.add(someSectionText);

                someSectionText = new Paragraph("Number remaining Booth in this Zone: " + (zn.getNumberBooth() - listBooth.size()));
                section.add(someSectionText);
                //print list booth of zone
                for (int b = 1; b <= listBooth.size(); b++) {
                    Booth booth = listBooth.get(b - 1);
                    Exhibitor ex = loadExhibitor(booth.getIdExhibitor());
                    
                    someSectionText = new Paragraph("Booth: " + booth.getName());
                    section.add(someSectionText);

                    someSectionText = new Paragraph("Was rent by " + ex.getId() + " - " + ex.getName() + " belong with company " + ex.getCompany());
                    section.add(someSectionText);    

                    someSectionText = new Paragraph("Money Service: " + booth.getMoneyService());
                    section.add(someSectionText);

                    someSectionText = new Paragraph("Date Rent: " + booth.getDateCreate());
                    section.add(someSectionText);
                    section.add(new Paragraph(""));
                }
            }

            document.add(chapter1);
            document.close();

            //Open file
            Runtime.getRuntime().exec("rundll32 url.dll,FileProtocolHandler " + path);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Create report cash receipts by date. check in database
     * which Exhibitor was paid, print out money and date that Exhibitor
     * paymet, then export to *.pdf file
     * and open report
     */
    public void receiptsByDate() {
        try {
            //Create a file
            String path = "ReceiptByDateReport.pdf";
            File file = new File(path);
            //Instantiation of document object
            Document document = new Document(PageSize.A4, 50, 50, 50, 50);
            //Creation of PdfWriter object
            PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(file));
            document.open();

            //title
            Paragraph title1 = new Paragraph("\t\tCash receipts by date of Exhibitor", FontFactory.getFont(FontFactory.HELVETICA, 20, Font.NORMAL, new BaseColor(0, 0, 0)));
            Chapter chapter1 = new Chapter(title1, 1);
            //load listdate Exhibitor paid
            Vector<String> listDate = loadPaidDate();

            for (int i = 0; i < listDate.size(); i++) {
                String date = listDate.get(i);
                Vector<Exhibitor> listExhibitor = loadExByDate(date);
                //title of paragraph
                title1 = new Paragraph(" " + listDate.get(i),
                        FontFactory.getFont(FontFactory.TIMES_ROMAN, 14, Font.UNDERLINE, new BaseColor(0, 0, 0)));

                Section section = chapter1.addSection(title1);
                //print content
                for(int x = 0; x < listExhibitor.size(); x++){
                    Exhibitor ex = listExhibitor.get(x);

                    Paragraph someSectionText = new Paragraph(ex.getId() + " - " + ex.getName() + " belong with company " + ex.getCompany() + "Paid money.");
                    section.add(someSectionText);
                }
            }

            document.add(chapter1);
            document.close();

            //Open file
            Runtime.getRuntime().exec("rundll32 url.dll,FileProtocolHandler " + path);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Report about status of every Exhibior
     */
    public void statusReport() {
    }

    /**
     * Report end of month
     */
    public void endOfMonthReport() {
    }

    /**
     * deposit slips Report be print folow by a Exhibitor
     * @param idExhibitor is specify a exhibitor
     */
    public void depositSlip(String idExhibitor) {
    }

    /**
     * Statement be print folow by a Exhibitor
     * @param idExhibitor is specify a exhibitor
     */
    public void statement(String idExhibitor) {
    }

    /**
     * Load list paid date of exhibitors
     * @return a vector is list paid date
     */
    public Vector<String> loadPaidDate(){
        Vector<String> listDate = new Vector<String>();

         try {
            ResultSet rs = DataUtility.executeProcedure("PaidDate", true);
            while (rs.next()) {

                String str = rs.getString("PaidDate");
                listDate.add(str);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return listDate;
    }

    /**
     * Check in data base which exhibitor payment folow by date
     * @param date is specify paid date
     * @return list exhibitor payment
     */
    public Vector<Exhibitor> loadExByDate(String date){

        Vector<Exhibitor> listExhibitors = new Vector<Exhibitor>();

        HashMap fiels = new HashMap();
        fiels.put("date", date);

        try {
            ResultSet rs = DataUtility.executeProcedure("ExPaidByDate", fiels, true);
            while (rs.next()) {
                Exhibitor exhibitor = new Exhibitor();
                listExhibitors.add(exhibitor);

                exhibitor.setId(rs.getString("id"));
                exhibitor.setName(rs.getString("Name"));
                exhibitor.setCompany(rs.getString("Company"));
                exhibitor.setMoney(rs.getFloat("money"));
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return listExhibitors;
    }
    /**
     * Load list Exhibitor from database, every exhibitor must be has
     * status = 1
     * @return list Exhibitor
     */
    public Vector<Exhibitor> loadExhibitor() {
        Vector<Exhibitor> listExhibitor = new Vector<Exhibitor>();
        try {
            ResultSet rs = DataUtility.excuteQuery("Select * from Exhibitor where status = 1");
            while (rs.next()) {
                Exhibitor exhibitor = new Exhibitor();
                listExhibitor.add(exhibitor);

                exhibitor.setId(rs.getString("id"));
                exhibitor.setName(rs.getString("Name"));
                exhibitor.setAddress(rs.getString("address"));
                exhibitor.setComfirm(rs.getInt("Confirm"));
                exhibitor.setCompany(rs.getString("Company"));
                exhibitor.setEmail(rs.getString("email"));
                exhibitor.setFax(rs.getString("fax"));
                exhibitor.setMoney(rs.getFloat("money"));
                exhibitor.setPaidStatus(rs.getInt("paidStatus"));
                exhibitor.setPhone(rs.getString("Phone"));
                exhibitor.setStatus(rs.getInt("status"));
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return listExhibitor;
    }

    /**
     * Load a exhibitor has id = idExhibitor in database
     * @param idExhibitor
     * @return a object exhibitor
     */
    public Exhibitor loadExhibitor(String idExhibitor) {
        Exhibitor exhibitor = new Exhibitor();
        try {
            ResultSet rs = DataUtility.excuteQuery("Select * from Exhibitor where id = " + "\'" + idExhibitor + "\'");
            rs.next();

            exhibitor.setId(rs.getString("id"));
            exhibitor.setName(rs.getString("Name"));
            exhibitor.setCompany(rs.getString("Company"));
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return exhibitor;
    }
}
